# -*- coding: utf-8 -*-
import traceback
from datetime import datetime, timedelta
from openpyxl import load_workbook, Workbook
from openpyxl.styles import colors, Font, Color, PatternFill, Alignment, numbers
from sqlalchemy import create_engine, event, text
import decimal
from sqlalchemy.orm import sessionmaker
from logging.handlers import RotatingFileHandler
import logging
import copy
from os import path

from config.config import CFG
from urllib.parse import quote_plus as urlquote
import io, re
from log import log
import string
import xlsxwriter
from urllib.parse import quote
from fastapi.responses import StreamingResponse
from database import engine
from openpyxl.utils import get_column_letter, range_boundaries
from errors.exception import BaseAPIException
from openpyxl.cell.text import InlineFont, RichText
from openpyxl.cell.rich_text import TextBlock, CellRichText
from utility.utility import dateFormat, ws_replace, is_placeholder_tpl, copy_cells_by_pattern


class MyDBQuery:
    """
    [使用pyodbc]
    生产环境driver: /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.5.so.2.1
    开发环境driver: /usr/local/lib/libmsodbcsql.17.dylib
    q = MyDBQuery(server = '124.223.159.103:1433',username = 'yg_crm',password = 'YG_crm1223',database = 'YG_CRM',driver='/usr/local/lib/libmsodbcsql.17.dylib')
    """

    # 数组参数pyodbc不支持；pymssql全支持
    # res = q.qToArr("select * from YG_CityName where city_shortname in :params", ['HGH', 'YVR'])
    # res = q.qToArr("select * from YG_CityName where city_shortname in :params", 'YVR')

    def __init__(self, server, username, password, database, driver='', log_path='/tmp/sqlalchemy.log', sql_engine=None):
        logger = logging.getLogger('sqlalchemy.engine')
        # 配置日志文件的处理器
        log_file_handler = RotatingFileHandler(log_path, maxBytes=1024 * 1024 * 100, backupCount=20)
        log_file_handler.setFormatter(logging.Formatter('%(asctime)s %(levelname)s: %(message)s [%(name)s]'))
        # 设置日志级别
        logger.setLevel(logging.DEBUG)
        # 将处理器添加到日志记录器
        logger.addHandler(log_file_handler)
        # 界面日志，echo=True
        if not sql_engine:
            if driver == '':
                # mssql_engine = create_engine(f'mssql+pymssql://{username}:{password}@{server}/{database}', echo=True)
                sql_engine = create_engine(f'mssql+pymssql://{username}:{urlquote(password)}@{server}/{database}')
            else:
                sql_engine = create_engine(
                    f'mssql+pyodbc://{username}:{urlquote(password)}@{server}/{database}?driver={driver}',
                    # disable default reset-on-return scheme
                    pool_reset_on_return=None,
                )

                @event.listens_for(sql_engine, "reset")
                def _reset_mssql(dbapi_connection, connection_record, reset_state):
                    if not reset_state.terminate_only:
                        dbapi_connection.execute("{call sys.sp_reset_connection}")
                    # so that the DBAPI itself knows that the connection has been
                    # reset
                    dbapi_connection.rollback()
        # 创建会话
        Session = sessionmaker(bind=sql_engine)
        self.Session = Session

    def qToObj(self, sql, params=None, key='id'):
        with self.Session() as session:
            # 执行SQL
            result = session.execute(text(sql), {'params': params})
            # 处理结果
            result_dict = {}
            for row in result:
                if hasattr(row, key):
                    # print(row.city_shortname)
                    k = getattr(row, key)
                    result_dict[k] = row
                else:
                    print(f"Row does not have column: {key}")
        return result_dict

    def qToArr(self, sql, params=None, deepArr=False):
        with self.Session() as session:
            # 执行SQL
            result = session.execute(text(sql), {'params': params})
            rows = result.fetchall()
            # print(type(rows))
            # 将结果转换为列表
            list_of_rows = [list(row) for row in rows] if deepArr else rows
        return list_of_rows


class One2Many:

    def __init__(self, df, sheet_name, biz_params, save_path=""):
        self.df = df  # dataframe数据（列表）
        self.sheet_name = sheet_name  # sheet名称（列表）
        self.path = path  # excel保存地址
        self.feeRate = biz_params['feeRate']
        self.YVR_transfer = biz_params.get('YVR_transfer', "yes")
        self.tpl_row_heights = []
        self.tpl_cls_widths = {}
        self.tpl_cls_hidden = []
        self.tpl_ranges = []
        self.tpl_styles = {}
        self.tpl_print_setting = []

    def __get_styles(self,tpl_path = None, sheet = None):
        if not sheet:
            wb = load_workbook(tpl_path)
            sheet = wb.active
        # 打印
        margin_left = sheet.page_margins.left
        margin_right = sheet.page_margins.right
        margin_top = sheet.page_margins.top
        margin_bottom = sheet.page_margins.bottom
        margin_header = sheet.page_margins.header
        margin_footer = sheet.page_margins.footer
        self.tpl_print_setting = [margin_left, margin_right, margin_top, margin_bottom, margin_header, margin_footer]
        # 行高，列宽
        tpl_row_heights = []
        tpl_cls_widths = {}
        tpl_cls_hidden = []
        for i in range(1, 55):
            height = sheet.row_dimensions[i].height
            hidden = sheet.row_dimensions[i].hidden
            # print(f"第{i}行的高度是: {height}")
            tpl_row_heights.append([height, hidden])
        # for i in 'ABCDEFGHIJHLMNOPQ':
        for i in range(18):
            k = get_column_letter(i + 1)
            column_width = sheet.column_dimensions[k].width
            tpl_cls_widths[k] = column_width
            if sheet.column_dimensions[k].hidden:
                tpl_cls_hidden.append(k)

        self.tpl_row_heights = tpl_row_heights
        self.tpl_cls_widths = tpl_cls_widths
        self.tpl_cls_hidden = tpl_cls_hidden

        # 样式
        tpl_styles = {}
        for r in range(1, 55):
            for i in range(18):
                c = get_column_letter(i + 1)
                source_cell = sheet[f'{c}{r}']
                loc = f'{c}{r}'
                if source_cell.has_style:
                    tpl_styles[loc] = {}
                    # print(f'{c}{r}',source_cell._style)
                    tpl_styles[loc]['_style'] = copy.copy(source_cell._style)
                    tpl_styles[loc]['font'] = copy.copy(source_cell.font)
                    tpl_styles[loc]['border'] = copy.copy(source_cell.border)
                    tpl_styles[loc]['fill'] = copy.copy(source_cell.fill)
                    tpl_styles[loc]['number_format'] = copy.copy(source_cell.number_format)
                    tpl_styles[loc]['protection'] = copy.copy(source_cell.protection)
                    tpl_styles[loc]['alignment'] = copy.copy(source_cell.alignment)
        self.tpl_styles = tpl_styles

        # 单元格合并情况
        tpl_ranges = []
        for merge in sheet.merged_cells.ranges:
            tpl_ranges.append(merge.coord)
        self.tpl_ranges = tpl_ranges

    def __str_process(self, s: str):
        return s.replace('\n', ' ').strip().upper()

    def __specs_xm(self, specs: str, volume: str=""):
            result = ""
            spec_arr = []
            tray_arr = []
            for index, s in enumerate(specs.split()):
                if s:
                    spec_arr.append(s + " PLTS")
                    tray_arr.append("OVER PACK USED#" + str(index + 1))
            trays = " ".join(tray_arr)
            specs = "\n".join(spec_arr)
            if volume: volume = "V: " + volume + "CBM"+"\n"
            result = """{{specs}}
{{volume}}
NOT RESTRICTED LITHIUM ION
BATTERIES IN COMPLIANCE WITH 
SECTION II OF PI 967

{{trays}}
"""
            result = result.replace("{{specs}}", specs).replace("{{volume}}", volume).replace("{{trays}}",trays).strip()
            return result

    def __digital_highlight(self, s: str, cell_font):
        if not any(char.isdigit() for char in s):
            return s
        blue = InlineFont(color='457DFF', sz=cell_font.size, family=cell_font.family, b=True)
        red = InlineFont(color='FF0000', sz=cell_font.size, family=cell_font.family)
        rich_text = RichText()
        result = re.split(r'\s+', s)
        for i in range(1, len(result) * 2 - 1, 2):
            result.insert(i, "\xA0")  # 插入富文本的空格
        log.info("加入间隔空格:{0}".format(result))

        rich_text = CellRichText([TextBlock(blue, s) if s.isdigit() else TextBlock(red, s) for s in result])
        log.info(rich_text)
        return rich_text

    def __check_colums(self, columns):
        must_have_columns = ['货物类型', '主单号', '分单号', '始发港', '货物目的地三字代码', '航班号航班日期', '件数', '重量', '品名', '尺寸数量', '体积', '包裹数', '发件人',
                             '收件人', '通知人信息', '其他传输信息', '备注']
        lacking_columns = []
        for c in must_have_columns:
            if c not in columns:
                lacking_columns.append(c)
        if len(lacking_columns) > 0:
            raise BaseAPIException(detail='表格缺少 "{0}" 字段！'.format('","'.join(lacking_columns)))


    def render(self,  tpl_wb, tpl_ws, sourceData, bill_field_name:str = '主单号'):
        log.info("占位符模式..........................")
        for row in sourceData:
            # 深度复制整个工作簿，不会复制样式
            # new_wb = deepcopy(original_wb)
            tpl_ws.sheet_state = 'visible'
            new_ws = tpl_wb.copy_worksheet(tpl_ws)
            # 修改新工作表名称（可选）
            new_ws.title = "主单"
            billNo = row[bill_field_name]
            print("billNo:", billNo)
            if not billNo:
                log.error("{}主单号为空！", billNo)
                continue

            ws_replace(new_ws, row)
            tpl_ws.sheet_state = 'hidden'
            print("save object:", {"tpl_wb": tpl_wb, "filename": f"waybill_{billNo}_waybill.xlsx"})
            yield {"tpl_wb": tpl_wb, "filename": f"waybill_{billNo}_waybill.xlsx"}
            tpl_wb.remove(new_ws)

    def save(self, tpl_file=None, bill_field_name:str = '主单号'):
        tpl_wb = load_workbook(tpl_file, keep_vba=True)
        try:
            # 样式模板
            ## 模板改名为“示例模板”，并将其移动到最后
            sheets = tpl_wb._sheets
            tpl_ws = tpl_wb["主单"]
            from_idx = sheets.index(tpl_ws)
            tpl_ws.title = '示例模板'
            if len(sheets)>1:
                sheets.insert(len(sheets), sheets.pop(from_idx))
            ## 模板类型
            placeholder_pattern = re.compile("{{\w+}}")
            isPlaceholderTpl = is_placeholder_tpl(tpl_ws, placeholder_pattern)

            # 字典数据预加载
            sourceData = []
            city = set()
            ## 大城市简码转全码，需要查数据库，需要预处理
            for row in self.df.itertuples():
                for col in self.df.columns:
                    if col in ['货物目的地三字代码', '始发港']:
                        city.add(getattr(row,col).strip())
            ## print(city.keys())
            db = CFG().cfg.db
            if db.type == 'mssql':
                q = MyDBQuery(server=db.server, username=db.username, password=db.password, database=db.database)
            else:
                q = MyDBQuery(server=db.server, username=db.username, password=db.password, database=db.database,
                              sql_engine=engine)
            dictData = q.qToObj("select * from YG_CityName where city_shortname in :params", list(city),'city_shortname')
            ## 验证转换字典是否有缺的
            # print(dictData)
            for c in city:
                if c not in dictData:
                    raise BaseAPIException(status_code=404, detail=f"简码'{c}'未查到城市全称")

            # 样式变量预设
            red_color = Color('FF0200')
            blue_color = Color('0000FF')
            font14 = Font(name=u'Times New Roman', bold=False, italic=False, size=14)
            font14_red = copy.deepcopy(font14)
            font14_red.color = red_color
            font10 = Font(name=u'Times New Roman', bold=False, italic=False, size=10)
            font10_red = copy.deepcopy(font10)
            font10_red.color = red_color
            font9 = Font(name=u'Times New Roman', bold=False, italic=False, size=9)
            font9_red = copy.deepcopy(font9)
            font9_red.color = red_color
            font8 = Font(name=u'Times New Roman', bold=False, italic=False, size=8)
            font8_red = copy.deepcopy(font8)
            font8_red.color = red_color
            font6 = Font(name=u'Times New Roman', bold=False, italic=False, size=6)
            font6_red = copy.deepcopy(font6)
            font6_red.color = red_color
            font7 = Font(name=u'Times New Roman', bold=False, italic=False, size=7)
            font7_red = copy.deepcopy(font6)
            font7_red.color = red_color
            font7_blue = copy.deepcopy(font6)
            font7_blue.color = blue_color
            align_L = Alignment(horizontal='left', vertical='bottom', wrap_text=True)
            align_R = Alignment(horizontal='right', vertical='bottom', wrap_text=True)
            align_C = Alignment(horizontal='center', wrap_text=True)
            align_LT = Alignment(horizontal='left', vertical='top', wrap_text=True)
            align_RT = Alignment(horizontal='right', vertical='top', wrap_text=True)
            align_CT = Alignment(horizontal='center', vertical='top', wrap_text=True)
            align_LC = Alignment(horizontal='left', vertical='center', wrap_text=True)
            align_RC = Alignment(horizontal='right', vertical='center', wrap_text=True)
            align_CC = Alignment(horizontal='center', vertical='center', wrap_text=True)

            # 数据转化部分
            now = datetime.now()
            volume_col = next(filter(lambda c: '体积' in c, self.df.columns), None)
            log.info("volume_col:",volume_col)
            for row in self.df.itertuples():
                r = {}
                for col in self.df.columns:
                    ## 修改或默认
                    ### 字符值要进一步加工，或生成对象
                    if '品名' in col:
                        r.update({col: self.__digital_highlight(self.__str_process(str(getattr(row, col)).strip()), font8_red)})
                    elif '尺寸数量' in col:
                        if not isPlaceholderTpl:
                            r.update({col: self.__str_process(f'DIMS(CM): {row.尺寸数量}')})
                        else:
                            r.update({col: self.__specs_xm(str(getattr(row, col)).strip(), str(getattr(row, volume_col)) if volume_col else "")})
                    elif '重量' in col or '体积' in col:
                        r.update({col: str(round(getattr(row, col),2))})
                    else:
                        r.update({col: str(getattr(row, col)).strip()})
                    ## 扩展增加字段
                    ### 港口城市全称
                    if "目的地" in col or "始发港" in col :
                        city_shortname = getattr(row, col)
                        city_fullname = dictData[city_shortname].city_fullname
                        r.update({f'{col}全称': city_fullname})
                    ### 主单号处理
                    elif col == bill_field_name:
                        billNo = str(getattr(row,col)).replace("-", "")
                        r.update([(f'{bill_field_name}1', billNo[:3]), (f'{bill_field_name}2', billNo[3:])])
                    ### 航班编码处理
                    elif col in ['航班号航班日期']:
                        try:
                            flight_code = row.航班号航班日期
                            flight_date_arr = flight_code.split('/')
                            flight_No = flight_date_arr[0]
                            flight_No_arr = flight_No.split("-")
                            flight_date = dateFormat(flight_date_arr[1])
                            r.update([('航班号航班日期1-1', flight_No_arr[0]), ('航班号航班日期1', flight_No),('航班号航班日期2', flight_date)])
                        except Exception as e:
                            traceback.print_exc()
                            raise BaseAPIException(status_code=404, detail=f"航班号航班日期格式错误")
                r.update({"当前日期": now.strftime("%Y-%m-%d"), "费率": self.feeRate})
                ### 默认值
                if "始发港" not in r: r.update({"始发港": "HGH"})
                sourceData.append(r)

            # 根据是否“占位符模板”模式，使用不同的保存方式
            if isPlaceholderTpl:
                return self.render(tpl_wb, tpl_ws, sourceData, bill_field_name)
            else:
                print("evaluate...")
                return self.evaluate(tpl_wb, tpl_ws, sourceData)
        except Exception as e:
            log.error(traceback.format_exc())
            raise BaseAPIException(status_code=500, detail=f"模板文件渲染异常：{e}")
        finally:
            if tpl_wb: tpl_wb.close()

    def evaluate(self, tpl_wb, tpl_ws, sourceData):
        log.info("普通模式..........................")
        #  # columns预处理，预处理前置了
        # for c in self.df.columns:
        #     if '\n' in c or '/' in c:
        #         self.df.rename(columns={c: c.replace('\n', '').replace('/', '')}, inplace=True)

        self.__check_colums(self.df.columns)

        # 有样本，则样式提取
        self.__get_styles(sheet=tpl_ws)
        # if tpl_file:
        #     self.__get_styles(tpl_file)
        ## 样式预设
        red_color = Color('FF0200')
        blue_color = Color('0000FF')
        font14 = Font(name=u'Times New Roman', bold=False, italic=False, size=14)
        font14_red = copy.deepcopy(font14)
        font14_red.color = red_color
        font10 = Font(name=u'Times New Roman', bold=False, italic=False, size=10)
        font10_red = copy.deepcopy(font10)
        font10_red.color = red_color
        font9 = Font(name=u'Times New Roman', bold=False, italic=False, size=9)
        font9_red = copy.deepcopy(font9)
        font9_red.color = red_color
        font8 = Font(name=u'Times New Roman', bold=False, italic=False, size=8)
        font8_red = copy.deepcopy(font8)
        font8_red.color = red_color
        font6 = Font(name=u'Times New Roman', bold=False, italic=False, size=6)
        font6_red = copy.deepcopy(font6)
        font6_red.color = red_color
        font7 = Font(name=u'Times New Roman', bold=False, italic=False, size=7)
        font7_red = copy.deepcopy(font6)
        font7_red.color = red_color
        font7_blue = copy.deepcopy(font6)
        font7_blue.color = blue_color
        align_L = Alignment(horizontal='left', vertical='bottom', wrap_text=True)
        align_R = Alignment(horizontal='right', vertical='bottom', wrap_text=True)
        align_C = Alignment(horizontal='center', wrap_text=True)
        align_LT = Alignment(horizontal='left', vertical='top', wrap_text=True)
        align_RT = Alignment(horizontal='right', vertical='top', wrap_text=True)
        align_CT = Alignment(horizontal='center', vertical='top', wrap_text=True)
        align_LC = Alignment(horizontal='left', vertical='center', wrap_text=True)
        align_RC = Alignment(horizontal='right', vertical='center', wrap_text=True)
        align_CC = Alignment(horizontal='center', vertical='center', wrap_text=True)
        # 循环读取excel，处理数据，样本样式复制，最后写入文件; 多条记录多个excel
        for row in sourceData:
            # 清空数据
            ws = tpl_wb.create_sheet(title="newSheet", index=0)
            tpl_ws.sheet_state = 'visible'
            copy_cells_by_pattern(tpl_ws, ws)
            # 源数值预处理
            bill_No = row['主单号']
            if not bill_No:
                continue
            port_start_s = row.get('始发港')
            port_start_full = row.get('始发港全称')
            port_end_s = row.get('货物目的地三字代码')
            port_end_full = row.get('货物目的地三字代码全称')
            # 写入数据到单元格
            ws['B1'] = row['主单号1']
            ws['B1'].font = font14
            ws['C1'] = port_start_s
            ws['C1'].font = font14
            ws['D1'] = row['主单号2']
            ws['D1'].font = font14_red
            ws.merge_cells('D1:F1')
            ws['L1'] = f'{row["主单号1"]}-'
            ws['L1'].font = font14
            ws['L1'].alignment = align_R
            ws['M1'] = row['主单号2']
            ws['M1'].font = font14
            ws['B3'] = row.get('发件人').upper()
            ws['B3'].font = font8_red
            ws.merge_cells('B3:H6')
            ws['B3'].alignment = align_LC
            ws['B9'] = row.get('收件人').upper()
            ws['B9'].font = font8_red
            ws.merge_cells('B9:H13')
            ws['B9'].alignment = align_LC
            ws.merge_cells('B14:H15')
            ws['B16'] = row.get('备注')
            ws['B16'].font = font9
            ws['I14'] = row.get('通知人信息').upper()
            ws['I14'].font = font8_red
            ws.merge_cells('I14:N18')
            ws['I14'].alignment = align_LC
            ws['B20'] = port_start_full
            ws['B20'].font = font10
            log.info("YVR_transfer: {}", self.YVR_transfer.upper())
            if port_end_s == 'YVR' or self.YVR_transfer.upper() == "NO":
                ws['B22'] = port_end_s
                ws['B22'].font = font10_red
            else:
                ws['B22'] = 'YVR'
                ws['B22'].font = font10_red
                ws['F22'] = port_end_s
                ws['F22'].font = font10_red
            print("freight_No_arr[0]: ", row.get('航班号航班日期1-1'))
            ws['C22'] = row.get('航班号航班日期1-1')
            ws['C22'].font = font10_red
            ws['C22'].alignment = align_C
            ws['B23'] = port_end_full
            ws['B23'].font = font10_red
            ws.merge_cells('B23:C23')
            ws['B22'].alignment = align_C
            ws['F23'] = row.get('航班号航班日期1')
            ws['F23'].font = font10_red
            ws['F23'].alignment = align_C
            ws['G23'] = row.get('航班号航班日期2')
            ws['G23'].font = font10_red
            ws['G23'].number_format = numbers.FORMAT_DATE_YYYYMMDD2
            ws.merge_cells('G22:H22')
            ws['G23'].alignment = align_C
            ws['H52'] = row.get('当前日期')
            ws['H52'].number_format = numbers.FORMAT_DATE_YYYYMMDD2
            ws.merge_cells('H52:I52')
            ws['H52'].alignment = align_CC
            ws['B31'] = row.get('件数')
            ws['B31'].font = font8_red
            ws['B31'].alignment = align_L
            # 重量要求只取整数，航空主单不能有小数点的
            ws['C31'] = row.get('重量')
            ws['C31'].font = font8_red
            ws['C31'].alignment = align_L
            ws['H31'] = self.feeRate
            ws['H31'].font = font8
            ws['H31'].alignment = align_C
            ws['L30'].value = row.get('品名')
            ws['L30'].font = font8_red
            ws['L30'].alignment = align_LC
            ws.merge_cells('L30:N33')
            # print("尺寸：", self.__str_process(f'DIMS(CM): {row.尺寸数量}'))
            ws['J34'] = row.get('尺寸数量')
            ws['J34'].font = font8_red
            ws['J34'].alignment = align_CC
            ws.merge_cells('J34:N41')
            ws['M42'] = row.get('体积')
            ws['M42'].font = font9_red
            ws['M42'].alignment = align_C
            if row.get('其他传输信息'):
                print(row.get('其他传输信息'))
                ws['B25'] = row.get('其他传输信息').upper()
                ws['B25'].font = font9_red
                ws.merge_cells('B25:N27')
                ws['B25'].alignment = align_LC
            ## 公式
            ws['F31'] = "=IF(ROUND(M42*166.66666,0)-C31>0,ROUND(M42*166.66666,0),C31)"
            ws['F31'].font = font8
            ws['F31'].alignment = align_L
            ws['D31'] = '=IF(ISBLANK(C31),"",IF(C31>45,"K  Q","K  N"))'
            ws['D31'].font = font8
            ws['D31'].alignment = align_C
            ws['J31'] = "=F31*H31"
            ws['J31'].font = font8
            ws['B42'] = "=B31"
            ws['B42'].font = font10
            ws['B42'].alignment = align_L
            ws['C42'] = "=C31"
            ws['C42'].font = font10
            ws['C42'].alignment = align_L
            ws['J42'] = "=J31"
            ws['J42'].font = font10
            ws['B44'] = "=J31"
            ws['B44'].font = font10
            ws['B44'].alignment = align_CC
            ws.merge_cells('B43:C43')
            ws['B51'] = "=J31"
            ws['B51'].font = font10
            ws['B51'].alignment = align_CC
            ws.merge_cells('B50:C50')
            ws['J52'] = "=B20"
            ws['J52'].font = font10
            ws['J52'].alignment = align_CC
            ws.merge_cells('J51:K51')
            ws['L52'] = "=L1"
            ws['L52'].font = font14
            ws['L52'].alignment = align_R
            ws['M52'] = "=M1"
            ws['M52'].font = font14
            ## 固定值
            ws['I20'] = "FREIGHT PREPAID"
            ws['I20'].font = font8_red
            ws.merge_cells('I19:N19')
            ws['I22'] = "CNY"
            ws['I22'].font = font9
            ws['J22'] = "PP       PP"
            ws['J22'].font = font9
            ws['J23'] = "NIL"
            ws['J23'].font = font9
            ws['L22'] = "NVD"
            ws['L22'].font = font9
            ws['M22'] = "NCV"
            ws['M22'].font = font9
            ws.merge_cells('M22:N22')
            ws['M21'].alignment = align_C
            ws['L42'] = "MEAS："
            ws['L42'].font = font9
            ws['L42'].alignment = align_R
            ws['N42'] = "CBM"
            ws['N42'].font = font9
            ws['N42'].alignment = align_L
            # 样式处理
            ## 行高
            if self.tpl_row_heights:
                row_heights = self.tpl_row_heights
            else:
                row_heights = [[48.0, False], [12.0, False], [19.0, False], [19.0, False], [19.0, False], [19.0, False], [1.05, False], [3.0, False], [13.0, False], [15.0, False],
                               [None, False], [15.0, False], [19.95, False], [13.05, False], [13.05, False], [19.95, False], [19.95, False], [19.95, False], [18.0, False],
                               [13.95, False], [19.95, False], [19.05, False], [18.0, False], [12.0, False], [21.0, False], [15.0, False], [10.05, False], [10.05, False],
                               [15.0, False], [15.0, False], [15.0, False], [14.25, False], [14.25, False], [14.25, False], [14.25, False], [14.25, False], [15.0, False],
                               [22.05, False], [7.05, False], [18.0, False], [15.0, False], [23.0, False], [None, False], [34.95, False], [34.95, False], [34.95, False],
                               [None, False], [None, False], [20.0, False], [21.0, False], [19.95, False]]

            if self.tpl_cls_widths:
                cln_widths = self.tpl_cls_widths
                # print("tpl:", cln_widths)
            else:
                cln_widths = {'A': 4.95833333333333, 'B': 5.05833333333333, 'C': 8.29166666666667, 'D': 5.175,
                              'E': 13.0, 'F': 10.4416666666667, 'G': 9.04166666666667, 'H': 7.75833333333333,
                              'I': 7.11666666666667, 'J': 10.225, 'L': 10.225, 'M': 8.29166666666667,
                              'N': 5.05833333333333}
                # print("fixed:", cln_widths)
            # print setting
            margin_left, margin_right, margin_top, margin_bottom, margin_header, margin_footer = self.tpl_print_setting
            ws.page_margins.left = margin_left
            ws.page_margins.right = margin_right
            ws.page_margins.top = margin_top
            ws.page_margins.bottom = margin_bottom
            ws.page_margins.header = margin_header
            ws.page_margins.footer = margin_footer
            ws.print_area = 'A1:N52'
            # 行高列宽复制
            for i in range(1, 54):
                if i - 1 < len(row_heights):
                    ws.row_dimensions[i].height = row_heights[i - 1][0]
                    ws.row_dimensions[i].hidden = row_heights[i - 1][1]

            for i in range(18):
                k = get_column_letter(i + 1)
                if k in cln_widths:
                    ws.column_dimensions[k].width = cln_widths[k]
            for c in self.tpl_cls_hidden:
                ws.column_dimensions[c].hidden = True
            # 样式复制
            if self.tpl_styles:
                for k in self.tpl_styles:
                    cell = ws[k]
                    cell._style = self.tpl_styles[k]['_style']
                    cell.font = self.tpl_styles[k]['font']
                    cell.border = self.tpl_styles[k]['border']
                    cell.fill = self.tpl_styles[k]['fill']
                    cell.number_format = self.tpl_styles[k]['number_format']
                    # print(k, self.tpl_styles[k]['alignment'])
                    cell.protection = self.tpl_styles[k]['protection']
                    cell.alignment = self.tpl_styles[k]['alignment']
                    # 富文本列，需单独再处理
                    if k == 'L30':
                        ws[k].value = row.get('品名')

            # 复制单元格合并情况
            # 合并前先取消过去
            # for rw in ws.iter_rows():
            #     for cell in rw:
            #         # 如果单元格是合并单元格的一部分，则取消合并
            #         if cell.merged:
            #             cell.unmerge()
            log.info("合并...")
            for r in self.tpl_ranges:
                ws.merge_cells(r)
            tpl_ws.sheet_state = 'hidden'
            ws.title = "主单"
            # 保存工作簿
            # wb.save(filename="{path}/{filename}".format(path=self.path, filename=f"waybill_{row.主单号}_waybill.xlsx"))
            print("save object:",{"tpl_wb": tpl_wb, "filename": f"waybill_{bill_No}_waybill.xlsx"})
            yield {"tpl_wb": tpl_wb, "filename": f"waybill_{bill_No}_waybill.xlsx"}
            tpl_wb.remove(ws)


"""
df = pd.read_excel("./data/bill_datasource.xlsx")
# df.columns
biz_params = {}
biz_params['feeRate'] = 30.97
writer1 = One2Many(df=[df], path='/Users/tonywu/Downloads/output_bills', sheet_name='主单', biz_params=biz_params)
writer1.save(sample_path='./data/waybill_489-60197211_waybill_new.xlsx')
# writer1.save()
print("well done!!")
"""


def is_number(s):
    result = False
    if type(s) in [int, float, complex, decimal.Decimal]:
        result = True
    # print(s, isinstance(s, (int, float, complex, decimal.Decimal)), result, type(s))
    return result


def export_excel(columns, data, fileName, sheetName, title, subtitle):
    # 将 DataFrame 转换为 Excel 文件
    buffer = io.BytesIO()
    # 生成.xlsx文件
    # workbook = xlsxwriter.Workbook(f'{filename}.xlsx')
    # 不写文件写IO
    workbook = xlsxwriter.Workbook(buffer)

    # 设置sheet页签名称
    worksheet = workbook.add_worksheet(sheetName if sheetName else fileName)

    # 标题
    title_format = workbook.add_format({'align': 'center',
                                        'valign': 'vcenter',
                                        "font_name": "宋体",
                                        'font_size': 15,
                                        "bold": True,
                                        "border": 1})
    # 副标题
    subtitle_format = workbook.add_format({'align': 'right',
                                           'valign': 'vcenter',
                                           'text_wrap': True,
                                           "font_name": "宋体",
                                           'font_size': 10,
                                           "border": 1})
    # 表头居中，背景灰色，字体颜色为白色
    column_header_format = workbook.add_format({'align': 'center',
                                                'valign': 'vcenter',
                                                'bg_color': '#7C9248',
                                                'font_size': 11,
                                                'font_color': 'white',
                                                "bold": True,
                                                "border": 1})

    # 表头居中，背景灰色，字体颜色为白色
    body_text_format_value = {'align': 'center',
                              'valign': 'vcenter',
                              'font_size': 11,
                              "border": 1}

    # 列数
    col_num = len(columns)
    # 标题部分
    worksheet.merge_range(0, 0, 0, col_num - 1, title, title_format)
    worksheet.merge_range(1, 0, 1, col_num - 1, subtitle, subtitle_format)
    worksheet.set_row(0, height=60)
    worksheet.set_row(1, height=45)
    # worksheet.write(0, 1, title, title_format)
    # worksheet.write(1, 1, subtitle, subtitle_format)

    # 写入表格的header
    # 26个英文字母，大写
    chars = string.ascii_uppercase

    column_format = {}
    column_writer = {}
    # 循环写入数据; 设置列名及宽度和数据填充
    for index, item in enumerate(data):
        # 设置列名及宽度
        for idx, col in enumerate(columns):
            dataIndex = col['dataIndex']
            val = index + 1 if dataIndex == "xuhao" else getattr(item, dataIndex)
            if index == 0:
                # 列名，宽度，确保只设一次
                col_name, col_width, col_align = col.get('title', dataIndex), col.get('width', None), col.get('align', 'center')
                col_code = chars[idx]
                # print(idx, col_code)
                # 写入列名
                worksheet.write_string(2, idx, col_name, column_header_format)
                worksheet.set_row(2, height=30)
                if col_width:
                    worksheet.set_column(f'{col_code}:{col_code}', col_width // 5)
                column_format[dataIndex] = workbook.add_format(body_text_format_value)
                if is_number(val):
                    align = col_align if col_align else "right"
                    column_writer[dataIndex] = worksheet.write_number
                    column_format[dataIndex].set_align(align)
                    if 'price' in dataIndex or 'amount' in dataIndex or 'fee' in dataIndex or 'freight' in dataIndex:
                        column_format[dataIndex].set_num_format('###0.0')
                elif 'date' in dataIndex:
                    align = col_align if col_align else "center"
                    column_writer[dataIndex] = worksheet.write_datetime
                    column_format[dataIndex].set_num_format('yyyy-mm-dd')
                    column_format[dataIndex].set_align(align)
                else:
                    align = col_align if col_align else "center"
                    column_writer[dataIndex] = worksheet.write
                    column_format[dataIndex].set_align(align)

            # 一条数据一行
            column_writer[dataIndex](index + 3, idx, val, column_format[dataIndex])
            worksheet.set_row(index + 3, height=25)

    workbook.close()

    # 将 Excel 文件转换为字节流，作为响应体返回给客户端
    buffer.seek(0)
    excel_bytes = buffer.getvalue()
    header = {}
    encoded_filename = quote(fileName.encode('utf-8'))
    content_disposition = f"attachment; filename*=UTF-8''{encoded_filename}"
    # header.update({'Content-Type': 'application/octet-stream'})
    header.update({f'Content-Disposition': content_disposition})
    return StreamingResponse(io.BytesIO(excel_bytes),
                             media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
                             headers=header)
